<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

/*
 * @name Departments_model
 * @type Model
 * @autor StackFeed LLC
 * @version 2.3
 *
 * @description - Departments model gets all the information from the datbase
 * related to a department and gives back to controller.
 */
class Departments_model extends CI_Model 
{
	/*
	 * Default constructor.
	 */
	function __construct()
	{
		parent::__construct();
	}
	
	/*
	 * Get all of the departments for a company.
	 */
	function get_company_departments($company_id)
	{
		$sql = "SELECT 	d.department_id,
						d.department_name,
						d.department_is_active,
						(SELECT count(*) FROM project WHERE project_department_id = d.department_id AND project_company_id = {$company_id}) department_num_projects,
					  	(SELECT count(*) FROM users WHERE user_department_id = d.department_id AND user_company_id = {$company_id}) department_num_users
			 	   FROM department d
				  WHERE department_company_id = {$company_id}
			   ORDER BY d.department_id DESC;";
		
		$query = $this->db->query($sql);
		
		// If nothing was returned invalid query.
		if($query->num_rows() == 0)
		{
			return false;
		}
		
		return $query->result_array();
	}
	
	/*
	 * Viewing a single department.
	 */
	function get_individual_department($company_id, $department_id)
	{
		$sql = "SELECT department_name,
					   department_description,
					   (SELECT count(*) FROM tickets WHERE tickets_department_id = {$department_id} AND tickets_company_id = {$company_id} AND tickets_status = 103) department_num_tickets_completed,
					   (SELECT count(*) FROM tickets WHERE tickets_department_id = {$department_id} AND tickets_company_id = {$company_id} AND tickets_status <> 103) department_num_tickets_open,
					   (SELECT count(*) FROM users WHERE user_department_id = {$department_id} AND user_company_id = {$company_id}) department_num_users,
				       (SELECT count(*) FROM project WHERE project_department_id = {$department_id} AND project_company_id = {$company_id}) department_num_projects,
				       (SELECT count(*) FROM departments_files WHERE departments_files_department_id = {$department_id} AND departments_files_company_id = {$company_id}) department_num_files,
					   department_id,
					   department_is_active,
					   department_is_default,
					   department_creation_date
			  	  FROM department
			 	 WHERE department_company_id = {$company_id}
			   	   AND department_id = {$department_id};";
		
		$query = $this->db->query($sql);
		
		// If nothing was returned invalid query.
		if($query->num_rows() == 0)
		{
			return false;
		}
		
		return $query->result_array();
	}
	
	/*
	 * Get a list of all the projects that are in a department.
	 */
	function get_department_projects($company_id, $department_id)
	{
		$sql = "SELECT p.project_id,
					   p.project_name
				  FROM project p
				 WHERE p.project_company_id = {$company_id}
				   AND p.project_department_id = {$department_id}
			  ORDER BY p.project_id DESC;";
		
		$query = $this->db->query($sql);
		
		// If nothing was returned invalid query.
		if($query->num_rows() == 0)
		{
			return false;
		}
		
		return $query->result_array();
	}
	
	/*
	 * Get all of the open tickets for a department.
	 */
	function get_open_tickets($company_id, $department_id)
	{
		$sql = "SELECT t.tickets_id,
					   t.tickets_title,
					   (SELECT enum_short_description FROM enum WHERE enum_id = t.tickets_status) tickets_status_description,
					   (SELECT enum_short_description FROM enum WHERE enum_id = t.tickets_severity) tickets_severity_description,
					   t.tickets_desired_completion
		  	      FROM tickets t
		 		 WHERE t.tickets_company_id = {$company_id}
		   		   AND t.tickets_department_id = {$department_id}
	     		   AND t.tickets_status <> 103
	     	  ORDER BY t.tickets_id DESC;";
		
		$query = $this->db->query($sql);
		
		// If nothing was returned invalid query.
		if($query->num_rows() == 0)
		{
			return false;
		}
		
		return $query->result_array();
	}
	
	/*
	 * Get all of the closed tickets for a department.
	 */
	function get_closed_tickets($company_id, $department_id)
	{
		$sql = "SELECT t.tickets_id,
					   t.tickets_title,
					   (SELECT enum_short_description FROM enum WHERE enum_id = t.tickets_status) tickets_status_description,
					   (SELECT enum_short_description FROM enum WHERE enum_id = t.tickets_severity) tickets_severity_description,
					   t.tickets_desired_completion
			      FROM tickets t
				 WHERE t.tickets_company_id = {$company_id}
				   AND t.tickets_department_id = {$department_id}
				   AND t.tickets_status = 103
			  ORDER BY t.tickets_id DESC;";
	
		$query = $this->db->query($sql);
	
		// If nothing was returned invalid query.
		if($query->num_rows() == 0)
		{
			return false;
		}
	
		return $query->result_array();
	}
	
	/*
	 * Get all of the useres in the department.
	 */
	function get_users($company_id, $department_id)
	{
		$sql = "SELECT u.first_name,
				       u.last_name,
				       u.id,
					   u.job_title
			      FROM users u,
				       department d
			 	 WHERE d.department_id = {$department_id}
				   AND d.department_company_id = {$company_id}
				   AND u.user_department_id = d.department_id
			  ORDER BY u.id DESC;";
	
		$query = $this->db->query($sql);
	
		// If nothing was returned invalid query.
		if($query->num_rows() == 0)
		{
			return false;
		}
	
		return $query->result_array();
	}
	
	/*
	 * Get all of the files uploaded to that department.
	 */
	function get_department_attachments($company_id, $department_id)
	{
		$sql = "SELECT df.departments_files_description,
					   df.departments_files_hash_name,
					   df.departments_files_real_name,
					   df.departments_files_upload_date,
				       df.departments_files_id,
					   u.first_name,
					   u.last_name,
					   u.id
		  		  FROM departments_files df,
					   users u
			     WHERE df.departments_files_company_id = {$company_id}
				   AND df.departments_files_department_id = {$department_id}
				   AND df.departments_files_user_id = u.id
			  ORDER BY df.departments_files_id DESC;";
	
		$query = $this->db->query($sql);
	
		// If nothing was returned invalid query.
		if($query->num_rows() == 0)
		{
			return false;
		}
	
		return $query->result_array();
	}
	
	/*
	 * Upload a file to the server.
	 */
	function upload($company_id, $department_id, $user_id, $real_name, $hash_name, $type, $size_kb, $size_mb, $description)
	{
		$sql = "INSERT INTO departments_files
					  (departments_files_real_name,
					   departments_files_hash_name,
					   departments_files_type,
					   departments_files_size_kb,
	         		   departments_files_size_mb,
					   departments_files_description,
					   departments_files_company_id,
				   	   departments_files_user_id,
					   departments_files_department_id,
					   departments_files_upload_date)
			   VALUES ({$real_name},
					   '{$hash_name}',
					   {$type},
					   {$size_kb},
					   {$size_mb},
					   {$description},
					   {$company_id},
					   {$user_id},
					   {$department_id},
					   NOW());";
	
		return $this->db->simple_query($sql);
	}
	
	/*
	 * Get departments hash name so it can be removed from the server.
	 */
	function get_hash_name($company_id, $file_id)
	{
		$sql = "SELECT d.departments_files_hash_name
				  FROM departments_files d
				 WHERE d.departments_files_company_id = {$company_id}
				   AND d.departments_files_id = {$file_id};";
	
		$query = $this->db->query($sql);
	
		// If nothing was returned invalid query.
		if($query->num_rows() == 0)
		{
			return false;
		}
	
		return $query->result_array();
	}
	
	/*
	 * Remove a file from the server.
	 */
	function remove_file($company_id, $file_id)
	{
		$sql = "DELETE
				  FROM departments_files
				 WHERE departments_files_company_id = {$company_id}
				   AND departments_files_id = {$file_id};";
	
		return $this->db->simple_query($sql);
	}
	
	/*
	 * Updating a departments information.
	 */
	function update_information($company_id, $department_id, $name, $description)
	{
		$sql = "UPDATE department
				   SET department_name = {$name},
					   department_description = {$description}
				 WHERE department_company_id = {$company_id}
				   AND department_id = {$department_id};";
		
		return $this->db->simple_query($sql);
	}
	
	/*
	 * Update the users assigned to the department.
	 */
	function update_assigned($company_id, $department_id, $user_id)
	{
		$sql = "UPDATE users
				   SET user_department_id = {$department_id}
				 WHERE id = {$user_id}
			       AND user_company_id = {$company_id};";
		
		return $this->db->simple_query($sql);
	}
	
	/*
	 * Update the projects assigned to the department.
	 */
	function update_projects($company_id, $department_id, $project_id)
	{
		$sql = "UPDATE project
				   SET project_department_id = {$department_id}
				 WHERE project_id = {$project_id}
				   AND project_company_id = {$company_id};";
				   		
		return $this->db->simple_query($sql);
	}
	
	/*
	 * Inserting a new department.
	 */
	function insert_department($company_id, $name, $description)
	{
		$sql = "INSERT INTO department
							(department_name,
							 department_description,
							 department_company_id,
							 department_creation_date,
							 department_last_update_date)
					  VALUES({$name},
					  		 {$description},
					  		 {$company_id},
					  		 NOW(),
					  		 NOW());";
		
		$query = $this->db->simple_query($sql);
		
		// If nothing was returned invalid query.
		if(!$query)
		{
			return false;
		}
		
		return $this->db->insert_id();
	}
	
	/*
	 * Deactivating a department.
	 */
	function deactivate($company_id, $department_id)
	{
		$sql = "UPDATE department
				   SET department_is_active = 0
				 WHERE department_company_id = {$company_id}
				   AND department_id = {$department_id};";
		
		return $this->db->simple_query($sql);
	}
	
	/*
	 * Activate a department.
	 */
	function activate($company_id, $department_id)
	{
		$sql = "UPDATE department
				   SET department_is_active = 1
				 WHERE department_company_id = {$company_id}
				   AND department_id = {$department_id};";
	
		return $this->db->simple_query($sql);
	}
	
	/*
	 * Deleting a department.
	 */
	function delete_department($company_id, $department_id)
	{
		$sql = "SELECT company_default_department_id
				  FROM company
				 WHERE company_id = {$company_id};";
		
		$query = $this->db->query($sql);
		
		// If nothing was returned invalid query.
		if($query->num_rows() == 0)
		{
			return false;
		}
		
		$query = $query->result_array();
		$default_department_id = $query[0]['company_default_department_id'];
		
		$sql = "UPDATE project
		   		   SET project_department_id = {$default_department_id}
		   		 WHERE project_company_id = {$company_id}
		   		   AND project_department_id = {$department_id};";
		
		if(!$this->db->simple_query($sql))
		{
			return false;
		}
		
		$sql = "UPDATE tickets
		   		   SET tickets_department_id = {$default_department_id}
		   		 WHERE tickets_company_id = {$company_id}
		   		   AND tickets_department_id = {$department_id};";
		
		if(!$this->db->simple_query($sql))
		{
			return false;
		}
		
		$sql = "UPDATE users
		   		   SET user_department_id = {$default_department_id}
		   		 WHERE user_company_id = {$company_id}
		   		   AND user_department_id = {$department_id};";
		
		if(!$this->db->simple_query($sql))
		{
			return false;
		}
		
		$sql = "UPDATE departments_files
		   		   SET departments_files_department_id = {$default_department_id}
		   		 WHERE departments_files_company_id = {$company_id}
		   		   AND departments_files_department_id = {$department_id};";
		
		if(!$this->db->simple_query($sql))
		{
			return false;
		}
		
		$sql = "DELETE 
		  		  FROM department
		  		 WHERE department_id = {$department_id}
		  		   AND department_company_id = {$company_id};";
		
		if(!$this->db->simple_query($sql))
		{
			return false;
		}
		
		return true;
	}
	
	/*
	 * Getting all of the comments associated with a departments file.
	 *
	 * @company_id    The ID of the company that is being queried.
	 * @department_id The ID of the department that is being queried.
	 * @$attachments  The array of ID's of files.
	 */
	function get_file_comments($company_id, $department_id, $attachments)
	{
		$comments = array();
		
		if($attachments)
		{
			for($i = 0; $i < count($attachments); ++$i)
			{
				$sql = "SELECT u.first_name,
							   u.last_name,
							   u.id,
							   dfc.department_files_comments_creation_date,
							   dfc.department_files_comments_description
						  FROM department_files_comments dfc,
							   users u
						 WHERE dfc.department_files_comments_company_id = {$company_id}
						   AND dfc.department_files_comments_department_id = {$department_id}
						   AND dfc.department_files_comments_file_id = {$attachments[$i]['departments_files_id']}
						   AND dfc.department_files_comments_user_id = u.user_id;";
	
				$query = $this->db->query($sql);
				$query = $query->result_array();
	
				array_push($comments, $query);
			}
		}
	
		return $comments;
	}
	
	/*
	 * Submitting a comment on a departments file.
	 *
	 * @company_id 	   The ID of the company that is being queried.
	 * @department_id  The ID of the department that is being queried.
	 * @file_id	   	   The ID of the file that is getting the comment.
	 * @user_id        The ID of the user that is creating the comment.
	 * @comment	       The comment sent back in the POST.
	 */
	function comment_on_file($company_id, $department_id, $file_id, $user_id, $comment)
	{
		$sql = "INSERT INTO department_files_comments
							(department_files_comments_company_id,
							 department_files_comments_department_id,
							 department_files_comments_file_id,
							 department_files_comments_user_id,
							 department_files_comments_description,
							 department_files_comments_creation_date,
							 department_files_comments_update_date)
					  VALUES({$company_id},
							 {$department_id},
							 {$file_id},
							 {$user_id},
							 {$comment},
							 NOW(),
							 NOW());";
	
		return $this->db->simple_query($sql);
	}
}